package com.vimi8.ebb.service;

import com.vimi8.ebb.dto.ProductBusinessVolumeDto;
import com.vimi8.ebb.dto.ProductTradingBusinessDto;
import com.vimi8.ebb.model.Address;
import com.vimi8.ebb.model.ElectricityBusiness;
import com.vimi8.ebb.model.GeoCodings;
import com.vimi8.ebb.model.ProductInfo;
import com.vimi8.ebb.sqlprovider.ElectricityBusinessSqlProvider;
import com.vimi8.ebb.sqlprovider.ProductInfoSqlProvider;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;

/**
 * Created by renyong on 2017/2/7.
 */
@Mapper
public interface PorductInfoService {
    /**
     * 查询产品信息多少条
     */
    @Select("<script>SELECT COUNT(1) FROM product_info " +
            "<if test=\"productName!=null and productName!='' \">where product_name like CONCAT('%',#{productName},'%')</if></script>")
    public int productInfoTotal(@Param("productName") String productName);
    @Select("<script>SELECT COUNT(m.id) FROM " +
            "(  " +
            " SELECT t.id AS id,SUM(t.business_volume) AS businessVolume,SUM(t.trading_volume) AS tradingVolume,t.sales_area AS salesArea,t.product_name AS productName," +
            " t.enum_category_id AS enumCategoryId,t.unit_price AS unitPrice,t.electricity_business_id AS electricityBusinessId,t.stock_volume AS stockVolume," +
            " t.stock_unit AS stockUnit,t.trading_time AS tradingTime,r.store_name AS electricityBusinessName FROM product_info AS t,electricity_business AS r" +
            "  WHERE  t.electricity_business_id = r.id " +
            "   <if test=\"startTime!=null and startTime!='' \">    AND DATE_FORMAT(t.trading_time,'%Y-%m')=#{startTime} </if>" +
            " <if test=\"addressCode!=null and addressCode!='' \"> AND r.address_code like concat('%',#{addressCode},'%')</if> "+
            " GROUP BY productName ORDER BY tradingVolume DESC" +
            "           )AS m</script>")
    public int productInfoTopListTotal(@Param("addressCode") String addressCode,@Param("startTime") String startTime);
    /**
     * 根据产品名称查询产品信息
     */
    @Select("<script>SELECT t.id as id,t.business_volume as businessVolume,t.trading_volume as tradingVolume,t.sales_area as salesArea,t.product_name as productName," +
            "t.enum_category_id as enumCategoryId,t.unit_price as unitPrice,t.electricity_business_id as electricityBusinessId,t.stock_volume as stockVolume," +
            "t.stock_unit as stockUnit,t.trading_time as tradingTime,r.store_name as electricityBusinessName FROM product_info as t,electricity_business as r where  t.electricity_business_id = r.id " +
            "<if test=\"productName!=null and productName!='' \"> and product_name like CONCAT('%',#{productName},'%')</if> " +
            "<if test=\"tradeBusinessOrder!=null and tradeBusinessOrder!='' and (tradeVolumnOrder==null or tradeVolumnOrder=='') \">  ORDER BY business_volume  ${tradeBusinessOrder} </if>" +
            "<if test=\"tradeVolumnOrder!=null and tradeVolumnOrder!='' and (tradeBusinessOrder==null or tradeBusinessOrder=='') \">  ORDER BY trading_volume  ${tradeVolumnOrder} </if>" +
            "<if test=\"tradeBusinessOrder!=null and tradeBusinessOrder!='' and tradeVolumnOrder!=null and tradeVolumnOrder!=''\">   ORDER BY trading_volume  ${tradeVolumnOrder}, business_volume  ${tradeBusinessOrder}</if>" +
            " limit #{start},#{size}</script>")
    public List<ProductInfo> getProductInfoList(@Param("productName") String productName, @Param("start") int start, @Param("size") int size, @Param("tradeBusinessOrder") String tradeBusinessOrder, @Param("tradeVolumnOrder") String tradeVolumnOrder);
    /**
     * 根据产品名称查询产品信息
     */
    @Select("<script>SELECT t.id as id,t.business_volume as businessVolume,t.trading_volume as tradingVolume,r.address_code as salesAreaId,t.product_name as productName," +
            "t.enum_category_id as enumCategoryId,t.unit_price as unitPrice,t.electricity_business_id as electricityBusinessId,t.stock_volume as stockVolume," +
            "t.stock_unit as stockUnit,t.trading_time as tradingTime,r.store_name as electricityBusinessName FROM product_info as t,electricity_business as r where  t.electricity_business_id = r.id " +
            "and r.address_code like CONCAT('%',#{geoCode},'%')" +
            "<if test=\"productName!=null and productName!='' \"> and product_name like CONCAT('%',#{productName},'%')</if> " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(trading_time,'%Y-%m')=#{startTime}</if> limit #{start},#{size}</script>")
    public List<ProductInfo> getProductInfoListElect(@Param("productName") String productName, @Param("geoCode") String geoCode, @Param("start") int start, @Param("size") int size,@Param("startTime") String startTime);


    /**
     * 根据产品名称查询产品信息
     */
    @Select("<script>SELECT t.id as id,t.business_volume as businessVolume,t.trading_volume as tradingVolume,r.address_code as salesAreaId,t.product_name as productName," +
            "t.enum_category_id as enumCategoryId,t.unit_price as unitPrice,t.electricity_business_id as electricityBusinessId,t.stock_volume as stockVolume," +
            "t.stock_unit as stockUnit,t.trading_time as tradingTime,r.store_name as electricityBusinessName FROM product_info as t,electricity_business as r where  t.electricity_business_id = r.id " +
            "and r.address_code like CONCAT('%',#{geoCode},'%')" +
            "<if test=\"productName!=null and productName!='' \"> and product_name like CONCAT('%',#{productName},'%')</if> " +
            "<if test=\"startTime!=null and startTime!='' \"> and trading_time &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> and trading_time &lt; #{endTime} </if> " +
            "limit #{start},#{size}</script>")
    public List<ProductInfo> getProductInfoListElectAdmin(@Param("productName") String productName, @Param("geoCode") String geoCode, @Param("start") int start, @Param("size") int size,
                                                     @Param("startTime") String startTime,
                                                     @Param("endTime") String endTime);


    /**
     * 根据产品名称查询产品信息<if test="startTime!=null and startTime!='' ">and trading_time Between #{startTime} and #{endTime}</if>
     */
    @Select("<script>SELECT t.id as id,t.business_volume as businessVolume,t.trading_volume as tradingVolume,r.address_code as salesAreaId,t.product_name as productName," +
            "t.enum_category_id as enumCategoryId,t.unit_price as unitPrice,t.electricity_business_id as electricityBusinessId,t.stock_volume as stockVolume," +
            "t.stock_unit as stockUnit,t.trading_time as tradingTime,r.store_name as electricityBusinessName FROM product_info as t,electricity_business as r " +
            "where  t.electricity_business_id = r.id and t.electricity_business_id =#{entity}<if test=\"productName!=null and productName!='' \"> and product_name like CONCAT('%',#{productName},'%')</if> limit #{start},#{size}</script>")
    public List<ProductInfo> getProductInfoEntityList(@Param("entity") String entity, @Param("productName") String productName, @Param("start") int start, @Param("size") int size);
    /**
     * 查询产品信息多少条
     */
    @Select("<script>SELECT COUNT(1) FROM product_info where electricity_business_id =#{entity}" +
            "<if test=\"productName!=null and productName!='' \">and product_name like CONCAT('%',#{productName},'%')</if></script>")
    public int productInfoTotalEntity(@Param("entity") String entity, @Param("productName") String productName);
    /**
     * 查询产品信息多少条
     */
    @Select("<script>SELECT COUNT(1) FROM product_info as a,electricity_business as r where r.address_code like CONCAT('%',#{geoCode},'%') and a.electricity_business_id = r.id " +
            "<if test=\"productName!=null and productName!='' \">and product_name like CONCAT('%',#{productName},'%')</if> " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(a.trading_time,'%Y-%m')=#{startTime}</if></script>")
    public int productInfoTotaldd(@Param("geoCode") String geoCode, @Param("productName") String productName,@Param("startTime") String startTime);


    /**
     * 查询产品信息多少条
     */
    @Select("<script>SELECT COUNT(1) FROM product_info as a,electricity_business as r where r.address_code like CONCAT('%',#{geoCode},'%') and a.electricity_business_id = r.id " +
            "<if test=\"productName!=null and productName!='' \">and product_name like CONCAT('%',#{productName},'%')</if> " +
            "<if test=\"startTime!=null and startTime!='' \"> and trading_time &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> and trading_time &lt; #{endTime} </if> " +
            "</script>")
    public int productInfoTotalddAdmin(@Param("geoCode") String geoCode, @Param("productName") String productName,
                                  @Param("startTime") String startTime,
                                  @Param("endTime") String endTime);

    /**
     * 根据产品名称查询产品信息
     */
    @Select("<script>SELECT t.id as id,t.business_volume as businessVolume,t.trading_volume as tradingVolume,t.sales_area as salesArea,t.product_name as productName," +
            "t.enum_category_id as enumCategoryId,r.store_name as electricityBusinessName,t.unit_price as unitPrice,t.electricity_business_id as electricityBusinessId,t.stock_volume as stockVolume," +
            "t.stock_unit as stockUnit,t.trading_time as tradingTime,r.store_name as electricityBusinessName FROM " +
            "product_info as t,electricity_business as r where  t.electricity_business_id = r.id and r.address_code like CONCAT('%',#{geoCode},'%') " +
            "<if test=\"productName!=null and productName!='' \"> and product_name like CONCAT('%',#{productName},'%')</if> " +
            "<if test=\"startTime!=null and startTime!='' \"> and trading_time Between #{startTime} and #{endTime}</if></script>")
    public List<ProductInfo> getProductInfoListName(@Param("productName") String productName, @Param("geoCode") String geoCode,@Param("startTime") String startTime, @Param("endTime") String endTime);



    /**
     * 根据产品ID查询产品信息
     */
    @Select("SELECT id,business_volume AS businessVolume,trading_volume as tradingVolume,sales_area as salesArea,product_name as productName," +
            "enum_category_id as enumCategoryId,unit_price as unitPrice,electricity_business_id as electricityBusinessId,stock_volume as stockVolume," +
            "stock_unit as stockUnit,trading_time as tradingTime from product_info WHERE id = #{id}")
    public ProductInfo getProductInfo(@Param("id") String id);
    /**
     * 根据产品销售金额或销售数量排序
     */
    @Select("<script>SELECT * FROM (select enum_category_id as enumCategoryId,SUM(business_volume) as businessVolume," +
            "SUM(trading_volume) as tradingVolume,product_name as productName from product_info p,electricity_business e where p.electricity_business_id=e.id and 1=1  " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!='' \">and e.address_code like concat('%',#{addressCode},'%')</if> " +
            "GROUP BY enum_category_id) as t ORDER BY businessVolume desc limit 0,10</script>")
    public List<ProductTradingBusinessDto> getAnalyzeInfoBusinessVolume(@Param("addressCode") String addressCode,@Param("startTime") String startTime);


    @Select("<script>select product_name as enumCategoryName,SUM(business_volume) as businessVolume," +
            "SUM(trading_volume) as tradingVolume,product_name as productName from product_info p,electricity_business e where p.electricity_business_id=e.id and 1=1 " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!='' \">and e.address_code like concat('%',#{addressCode},'%')</if> " +
            "GROUP BY product_name ORDER BY tradingVolume desc limit 0,10</script>")
    public List<ProductTradingBusinessDto> getAnalyzeInfoTradingVolume(@Param("addressCode") String addressCode, @Param("startTime") String startTime);
    @Select("<script>select product_name as enumCategoryName,SUM(business_volume) as businessVolume," +
            "TRUNCATE(SUM(trading_volume),2) as tradingVolume,product_name as productName from product_info p,electricity_business e where p.electricity_business_id=e.id and 1=1 " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!='' \">and e.address_code like concat('%',#{addressCode},'%')</if> " +
            "GROUP BY product_name ORDER BY tradingVolume desc limit #{start},#{size}</script>")
    public List<ProductTradingBusinessDto> getAnalyzeInfoList(@Param("addressCode") String addressCode, @Param("startTime") String startTime, @Param("start") int start, @Param("size") int size);

    /**
     * 添加产品信息
     */
    @Insert("INSERT INTO product_info(id,business_volume,trading_volume,sales_area,product_name,enum_category_id,unit_price,electricity_business_id,stock_volume,stock_unit,trading_time) " +
            "VALUES (#{id},#{businessVolume},#{tradingVolume},#{salesArea},#{productName},#{enumCategoryId},#{unitPrice},#{electricityBusinessId},#{stockVolume},#{stockUnit},#{tradingTime})")
    public int insertProductInfo(ProductInfo productInfo);

    @InsertProvider(type = ProductInfoSqlProvider.class, method = "getInsertProductSql")
    public int batchProductInfoBusiness(@Param("productInfoList")  List<ProductInfo> productInfoList);
    /**
     * 按ID删除产品信息
     */
    @Delete(" delete from product_info where 1=1 and " +
            "id=#{id}")
    public int deleteTraInfoById(@Param("id") String id);


    /**
     * 根据产品ids批量删除产品
     * @param ids
     * @return
     */
    @Delete(" delete from product_info where id in (${ids})")
    public Integer deleteProductInfoByIds(@Param("ids") String ids);

    /**
     *
     * @param ids
     * @return
     */
    @Delete("DELETE FROM electricity_business,product_info USING electricity_business ," +
            "product_info WHERE electricity_business.id IN (${ids}) " +
            " AND product_info.electricity_business_id=electricity_business.id")
    public int deleteSelectElectricityBusiness(@Param("ids") String ids);

    /**
     * 按区域查询销售量
     */
    @Select("<script>SELECT SUM(business_volume) as businessVolume FROM product_info as p,electricity_business as e WHERE " +
            "p.electricity_business_id = e.id and e.address_code like CONCAT('%',#{addressCode},'%') " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if></script>")
    public HashMap<String,Object> getProductBusinessVolume(@Param("addressCode") String addressCode, @Param("startTime") String startTime);


    /**
     * 按区域查询销售量
     */
    @Select("<script>SELECT SUM(trading_volume) AS ebbVolume FROM electricity_business AS e LEFT JOIN trading_detail AS t ON" +
            " t.`ebb_id` = e.id and e.address_code like CONCAT('%',#{addressCode},'%')" +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(t.`trade_date`,'%Y-%m')=#{startTime}</if></script>")
    public HashMap<String,Object> getEbbProductTradingVolume(@Param("addressCode") String addressCode, @Param("startTime") String startTime);


    /**
     * 按区域查询销售额
     */
    @Select("<script>SELECT SUM(trading_volume) as tradingVolume FROM product_info as p,electricity_business as e WHERE " +
            "p.electricity_business_id = e.id and e.address_code like CONCAT('%',#{addressCode},'%') " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if></script>")
    public HashMap<String,Object> getProductTradingVolume(@Param("addressCode") String addressCode, @Param("startTime") String startTime);
    /**
     * 按ID更新产品信息
     */
    @Update("UPDATE product_info SET business_volume=#{businessVolume},trading_volume=#{tradingVolume},sales_area=#{salesArea},product_name=#{productName}," +
            "enum_category_id=#{enumCategoryId},unit_price=#{unitPrice},stock_volume=#{stockVolume}," +
            "stock_unit=#{stockUnit},trading_time=#{tradingTime} where id=#{id}")
    public int updateProductInfo(ProductInfo productInfo);
/**
 * 产品top分析
 * */
    @Select("<script>select product_name as enumCategoryName,SUM(business_volume) as businessVolume,e.address_code as addressCode," +
            "SUM(trading_volume) as tradingVolume,product_name as productName from product_info p,electricity_business e where p.electricity_business_id=e.id and 1=1 " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!='' \">and e.address_code like concat('%',#{addressCode},'%')</if> " +
            "GROUP BY product_name ORDER BY tradingVolume desc limit 0,#{size}</script>")
    public List<ProductTradingBusinessDto> getTopStatics(@Param("addressCode") String addressCode, @Param("startTime") String startTime, @Param("size") int size);
    /**
     * 产品top分析
     * */
    @Select("<script>select count(1) from product_info p,electricity_business e where p.electricity_business_id=e.id and 1=1 " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!='' \">and e.address_code like concat('%',#{addressCode},'%')</if> " +
            "</script>")
    public int getTopStaticsCount(@Param("addressCode") String addressCode, @Param("startTime") String startTime);

}
